package com.wosai.register.repository.jdbc;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.lang.exception.ExceptionUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;

import com.wosai.register.model.Employee;
import com.wosai.register.model.EmployeeAccount;
import com.wosai.register.model.EmployeeCash;
import com.wosai.register.repository.EmployeeRepository;
import com.wosai.register.util.ExceptionUtil;
import com.wosai.register.util.StringUtil;

@Repository
public class JdbcEmployeeRepository implements EmployeeRepository {
    private final static Log logger = LogFactory.getLog(JdbcEmployeeRepository.class);

    private NamedParameterJdbcTemplate npjt;
    private SimpleJdbcInsert sji;
    private SimpleJdbcInsert sjiCash;
    private SimpleJdbcInsert sjiAccount;

    private final static String SELECT_BY_ID = "select * from EMPLOYEE where merchant_id=:merchantId  and id=:id";
    private final static String SELECT_BY_USERNAME = "select * from EMPLOYEE where merchant_id=:merchantId  and username=:username";
    private final static String SELECT_BY_TERMINAL = "select * from EMPLOYEE where merchant_id=:merchantId  ";
    
    private final static String DELETE = "delete from EMPLOYEE where merchant_id=:merchantId  and id=:id";
    private final static String DELETE_BY_TERMINAL = "delete from EMPLOYEE where merchant_id=:merchantId ";
    
    private final static String UPDATE = "update EMPLOYEE set username=:username, password=:password, status=:status, " +
    		                             "discount_priviledge=:discountPriviledge, report_priviledge=:reportPriviledge, " +
    		                             "return_priviledge=:returnPriviledge, system_setting_priviledge=:systemSettingPriviledge, " +
    		                             "user_flag=:userFlag, resv1=:resv1, resv2=:resv2, resv3=:resv3, resv4=:resv4, classno=:classno, " +
    		                             "branch_id=:branchId " +
    		                             "where merchant_id=:merchantId  and id=:id";

    private final static String SELECT_CASHES_BY_TERMINAL = "select * from EMPLOYEE_CASH where " +
    		                             "merchant_id=:merchantId and branch_id=:branchId and terminal_id=:terminalId ";

    private final static String SELECT_ACCOUNTS_BY_TERMINAL = "select * from EMPLOYEE_ACCOUNT where " +
                                         "merchant_id=:merchantId and branch_id=:branchId and terminal_id=:terminalId ";

    @Autowired
    public JdbcEmployeeRepository(DataSource dataSource) {
        this.sji = new SimpleJdbcInsert(dataSource)
                            .withTableName("EMPLOYEE");
        
        this.sjiCash = new SimpleJdbcInsert(dataSource)
                            .withTableName("EMPLOYEE_CASH");
        
        this.sjiAccount = new SimpleJdbcInsert(dataSource)
                            .withTableName("EMPLOYEE_ACCOUNT");

        this.npjt = new NamedParameterJdbcTemplate(dataSource);
    }

    @Override
    public Employee findById(String merchantId, String id)
    {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("merchantId", merchantId);
        params.put("id", id);
        
        return this.npjt.queryForObject(SELECT_BY_ID, params, ParameterizedBeanPropertyRowMapper.newInstance(Employee.class));
    }

    @Override
    public Employee findByUsername(String merchantId,
                               String username)
    {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("merchantId", merchantId);
        params.put("id", username);
        
        return this.npjt.queryForObject(SELECT_BY_USERNAME, params, ParameterizedBeanPropertyRowMapper.newInstance(Employee.class));
    }

    @Override
    public List<Employee> findByTerminal(String merchantId) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("merchantId", merchantId);
        
        return this.npjt.query(SELECT_BY_TERMINAL, params, ParameterizedBeanPropertyRowMapper.newInstance(Employee.class));
    }

    @Override
    public List<String> insertBatch(List<Employee> users) {
        SqlParameterSource[] batch = new SqlParameterSource[users.size()];
        for(int i=0; i<batch.length; ++i)
            batch[i] = new BeanPropertySqlParameterSource(users.get(i));

        ArrayList<String> failed = new ArrayList<String>();
        try{
            int[] rows = this.sji.executeBatch(batch);
            logger.info("batch inserted " + StringUtil.intArrayToString(rows));
        }catch(DataAccessException dae){
            List<Integer> failedIndices = ExceptionUtil.extractFailedStatements(dae);
            for(int i: failedIndices){
                failed.add(users.get(i).getId());
            }
            logger.warn("error in batch insert " + failedIndices);
            logger.warn("detailed error info " + ExceptionUtils.getStackTrace(dae));
        }
        return failed;
    }

    @Override
    public List<String> updateBatch(List<Employee> users) {
        SqlParameterSource[] batch = new SqlParameterSource[users.size()];
        for(int i=0; i<batch.length; ++i)
            batch[i] = new BeanPropertySqlParameterSource(users.get(i));

        ArrayList<String> failed = new ArrayList<String>();
        try{
            int[] rows = this.npjt.batchUpdate(UPDATE, batch);
            logger.info("batch updated " + StringUtil.intArrayToString(rows));
        }catch(DataAccessException dae){
            List<Integer> failedIndices = ExceptionUtil.extractFailedStatements(dae);
            for(int i: failedIndices){
                failed.add(users.get(i).getId());
            }
            logger.warn("error in batch update " + failedIndices);
            logger.warn("detailed error info " + ExceptionUtils.getStackTrace(dae));
        }

        return failed;
    }

    @Override
    public void delete(String merchantId, String id) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("merchantId", merchantId);
        params.put("id", id);
        
        int rows = this.npjt.update(DELETE, params);
        logger.info("deleted " + rows);
        
    }

    @Override
    public void deleteByTerminal(String merchantId) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("merchantId", merchantId);
        
        int rows = this.npjt.update(DELETE_BY_TERMINAL, params);
        logger.info("deleted " + rows);
    }

    @Override
    public List<EmployeeCash> findCashesByTerminal(String merchantId,
                                                   String branchId,
                                                   String terminalId) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("merchantId", merchantId);
        params.put("branchId", branchId);
        params.put("terminalId", terminalId);
        
        return this.npjt.query(SELECT_CASHES_BY_TERMINAL, params, ParameterizedBeanPropertyRowMapper.newInstance(EmployeeCash.class));
    }

    @Override
    public void insertCashBatch(List<EmployeeCash> cashes) {
        SqlParameterSource[] batch = new SqlParameterSource[cashes.size()];
        for(int i=0; i<batch.length; ++i)
            batch[i] = new BeanPropertySqlParameterSource(cashes.get(i));

        this.sjiCash.executeBatch(batch);
    }

    @Override
    public List<EmployeeAccount> findAccountsByTerminal(String merchantId,
                                                        String branchId,
                                                        String terminalId) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("merchantId", merchantId);
        params.put("branchId", branchId);
        params.put("terminalId", terminalId);
        
        return this.npjt.query(SELECT_ACCOUNTS_BY_TERMINAL, params, ParameterizedBeanPropertyRowMapper.newInstance(EmployeeAccount.class));
    }

    @Override
    public void insertAccountBatch(List<EmployeeAccount> accounts) {
        SqlParameterSource[] batch = new SqlParameterSource[accounts.size()];
        for(int i=0; i<batch.length; ++i)
            batch[i] = new BeanPropertySqlParameterSource(accounts.get(i));

        this.sjiAccount.executeBatch(batch);
    }



}
